from odoo import api, models, fields


class PurchaseReceivedPicking(models.Model):
    _name = 'purchase.received.picking.report'
    _description = 'Purchase Received Picking Report'
    _auto = False

    partner_id = fields.Many2one('res.partner', string='Supplier')
    purchase_id = fields.Many2one('purchase.order', string='Purchase Order')
    line_id = fields.Many2one('purchase.order.line', string='Purchase Order Line')
    product_id = fields.Many2one('product.product', string='Product')
    product_uom = fields.Many2one('uom.uom', string='Product Uom')
    product_qty = fields.Float(string='Purchase Quantity', digits='Product Price')
    qty_received = fields.Float(string='Receive Quantity', digits='Product Price')
    picking_ids = fields.Many2many('stock.picking', string='Pickings', compute='compute_fields')

    @api.depends('line_id')
    def compute_fields(self):
        for record in self:
            move_ids = self.env['stock.move'].search([('purchase_line_id', '=', record.line_id.id)])
            record.picking_ids = move_ids.picking_id.filtered(lambda x: x.state=='done').ids

    @property
    def _table_query(self):
        query = """
        SELECT
            row_number() OVER() AS id,
            po.partner_id,
            po.id as purchase_id,
            pol.id as line_id,
            pol.product_id,
            pol.product_uom,
            pol.product_qty,
            pol.qty_received
        FROM purchase_order_line pol
        LEFT JOIN purchase_order po on po.id=pol.order_id
        LEFT JOIN stock_move sm on pol.id=sm.purchase_line_id
        LEFT JOIN stock_picking sp on sp.id=sm.picking_id
        WHERE pol.state='purchase' and pol.product_qty!=pol.qty_received and sp.state='done'
        GROUP BY po.id,pol.id
        """
        return query
